#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : day_report_house_state.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-10-25 16:53 
@Desc    : 
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util
from report_system.utils import df_util
from report_system.utils import date_util

from report_system.utils import mail_util
from report_system.utils import mailbox_util
from report_system.utils.log_util import log
from report_system.utils.development_util import debug

if debug:
    receivers = 'bianzhiwei@iyoujia.com;zhangxian@city-home.cn'
    city_mails = mailbox_util.mails_test
else:
    receivers = "tanshenghu@city-home.cn;miaohaixia@city-home.cn;zhongjunyi@city-home.cn;" \
                "wudi@city-home.cn;wangsuxiao@city-home.cn;zhangxian@city-home.cn;songweiyun@iyoujia.com;" \
                "xuzhehang@iyoujia.com;zhuqiang@iyoujia.com;zhengliangl@sweetome.com"
    city_mails = mailbox_util.mails_all


def house_state(dt):
    sql = """select 
        case lh.own_type when 1 then '有家' when 2 then '城宿' when 3 then '斯维登' else '所有公司' end 公司,
        coalesce(city.short_name,'全国')  城市,
        '{dt}' 日期,
        sum(if(date(lhot.create_time) = '{dt}',1,0)) 新增上线房源,
        sum(if(date(lo14.create_time) = '{dt}',1,0)) 新增下线房源,
        sum(if(lh.state = 100,1,0)) 在线房源
        
        from youjia.lod_house lh
        left join youjia.lod_house_online_time lhot on lhot.lod_house_id=lh.id
        left join youjia.lod_house_online_time lhot1 on lhot1.lod_house_id=lh.id and lhot1.id<lhot.id
        left join youjia.lod_operations lo14  on lh.id=lo14.house_id  and lo14.ops_type=8   
        left join youjia.lod_operations lo15 on lh.id=lo15.house_id and lo15.ops_type=8 and lo14.id < lo15.id 
        left join youjia.city on city.id = lh.city_id
        where lhot1.id is null
        and lh.city_id <> 376
        and lo15.id is null
        group by lh.own_type,city.short_name
        with rollup""".format(dt=dt)
    return pd.read_sql_query(sql, db_util.con_youjia)


def process(dt=None):
    dt = dt if dt else date_util.curdate()
    house_state_df = house_state(dt)
    house_state_df = df_util.df_sort(house_state_df, '在线房源')
    df_dict = {'【房屋状态】': house_state_df}

    text = """    Dear All:
                附件是新版 房屋上线、下线、在线状态！ 
                
                请查收！
                谢谢！
        如有问题！
        请联系: dt@iyoujia.com
        """
    mail_util.distribute_mail(df_dict=df_dict, file_name='房屋状态', text=text, html_sheet_name='【房屋状态】',
                              title="【房屋状态】", receiver=receivers, na_rep='-', index=False,
                              city_mails=city_mails, city=True, city_key='城市', uuid='city', freeze_panes=(1, 3))


def run(dt=None):
    try:
        process(dt)
    except Exception as e:
        mail_util.error_mail("房屋状态", e.__str__())
        log.exception(e)


if __name__ == '__main__':
    run()
